﻿using System;
using System.Collections.Generic;
using System.Linq;
using MySql.Data.MySqlClient;
using eStock.SQL;

namespace eStock.DataObjects
{
    public class AddressCollection : DataCollection<Address>
    {
        public override Address GetById(int id)
        {
            return List.Cast<Address>().FirstOrDefault(c => c.Id == id);
        }

        public override void Replace(Address newValue)
        {
            List[IndexOf(GetById(newValue.Id))] = newValue;
        }

        public override bool Update()
        {
            try
            {
                SQLDataReader res;

                foreach (Address c in List)
                {
                    res = MainWindow.Database.QueryRead("SELECT * FROM es_addresses WHERE id = " + c.Id);
                    int i = MainWindow.Database.FetchCount(res);
                    if (i == 0)
                    {
                        ((Address)List[List.IndexOf(c)]).Id = Convert.ToInt32(MainWindow.Database.QueryWrite("INSERT INTO es_addresses (imgId, description,  company, street,  PLZ,  location,  country,  name,  phone,  mobile,  fax,  mail,  web,  customerId,  login,  UsSTID,  birthday,  bank,  BLZ,  konto,  SWIFT,  IBAN,  comments) " +
                                         "VALUES ('" + c.ImgId + "', '" + MainWindow.Database.EscapeString(c.Description) + "', '" + MainWindow.Database.EscapeString(c.Company) + "', '" + MainWindow.Database.EscapeString(c.Street) + "', '" + MainWindow.Database.EscapeString(c.PLZ) + "', '" + MainWindow.Database.EscapeString(c.Location) + "', '" + MainWindow.Database.EscapeString(c.Country) + "', '" + MainWindow.Database.EscapeString(c.Name) + "', '" + MainWindow.Database.EscapeString(c.Phone) + 
                                         "', '" + MainWindow.Database.EscapeString(c.Mobile) + "', '" + MainWindow.Database.EscapeString(c.Fax) + "', '" + MainWindow.Database.EscapeString(c.Mail) + "','" + MainWindow.Database.EscapeString(c.Web) + "','" + MainWindow.Database.EscapeString(c.CustomerId) + "','" + MainWindow.Database.EscapeString(c.Login) + "','" + MainWindow.Database.EscapeString(c.UsSTID) + "','" + c.Birthday.ToString("yyyy:MM:dd HH:mm:ss") + "','" + MainWindow.Database.EscapeString(c.Bank) + 
                                         "','" + MainWindow.Database.EscapeString(c.Blz) + "','" + MainWindow.Database.EscapeString(c.Konto) + "','" + MainWindow.Database.EscapeString(c.SWIFT) + "','" + MainWindow.Database.EscapeString(c.IBAN) + "','" + MainWindow.Database.EscapeString(c.Comments) + "', CURRENT_TIMESTAMP)"));
                    }
                    else
                    {
                        MainWindow.Database.QueryWrite("UPDATE es_addresses SET imgId = '" + c.ImgId + "', description = '" + MainWindow.Database.EscapeString(c.Description) + "',company = '" + MainWindow.Database.EscapeString(c.Company) + "',street = '" + MainWindow.Database.EscapeString(c.Street) + "',PLZ = '" + MainWindow.Database.EscapeString(c.PLZ) + "',location = '" + MainWindow.Database.EscapeString(c.Location) + "',country = '" + MainWindow.Database.EscapeString(c.Country) +
                                         "',name = '" + MainWindow.Database.EscapeString(c.Name) + "',phone = '" + MainWindow.Database.EscapeString(c.Phone) + "',mobile = '" + MainWindow.Database.EscapeString(c.Mobile) + "',fax = '" + MainWindow.Database.EscapeString(c.Fax) + "',mail = '" + MainWindow.Database.EscapeString(c.Mail) + "'," + "web = '" + MainWindow.Database.EscapeString(c.Web) + "',customerId = '" + MainWindow.Database.EscapeString(c.CustomerId) + "',login = '" + MainWindow.Database.EscapeString(c.Login) +
                                         "',UsSTID = '" + MainWindow.Database.EscapeString(c.UsSTID) + "',birthday = '" + c.Birthday.ToString("yyyy:MM:dd HH:mm:ss") + "',bank = '" + MainWindow.Database.EscapeString(c.Bank) + "',BLZ = '" + MainWindow.Database.EscapeString(c.Blz) + "',konto = '" + MainWindow.Database.EscapeString(c.Konto) + "',SWIFT = '" + MainWindow.Database.EscapeString(c.SWIFT) + "',IBAN = '" + MainWindow.Database.EscapeString(c.IBAN) + "',comments = '" + MainWindow.Database.EscapeString(c.Comments) + "', WHERE id = " + c.Id + ";");
                    }
                }
                res = MainWindow.Database.QueryRead("SELECT * FROM es_addresses");
                Dictionary<string, object> data = MainWindow.Database.FetchArray(res);
                List<int> removeIds = new List<int>();
                while (data != null)
                {
                    Address c = GetById(Convert.ToInt32(data["id"]));
                    if (c == null)
                    {
                        removeIds.Add(Convert.ToInt32(data["id"]));
                    }
                    data = MainWindow.Database.FetchArray(res);
                }
                if (removeIds.Count > 0)
                {
                    string sqlString = "DELETE FROM es_addresses WHERE ";
                    for (int j = 0; j < removeIds.Count; j++)
                    {
                        if (j != 0)
                            sqlString += " AND ";
                        sqlString += "id = " + removeIds[j];
                    }
                    MainWindow.Database.QueryWrite(sqlString);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
